/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Control;


import Boundary.TU;
import Boundary.siswa;
import java.lang.String;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.net.ssl.SSLContext;

/**
 *
 * @author FaveGanteng
 */
public class SiswaControl {
    private Connection conn;
    private boolean String;

     SiswaControl(Connection KoneksiDataBase) {
       this.conn =KoneksiDataBase;
    }

    public static SiswaControl getKoneksi() throws SQLException {
        SiswaControl kon = new SiswaControl(Tools.DataBaseConnection.KoneksiDataBase());
        return kon;
    }

     public siswa cekNamaSiswa (String nm) throws Exception {
         PreparedStatement psmt = null;
        ResultSet reset = null;
        conn.setAutoCommit(false);

            siswa s = new siswa();

        //   String nama =s.getNama();
          String nis = s.getNIS();

          String sql = "select nama from rpltsiswa "
                  + " where nis = '"  + nis + "'";
//                + "' and password like '" + passwd + "'" ;

        psmt = conn.prepareStatement(sql);
        reset = psmt.executeQuery();

//       siswa user =null;
//        if (reset.next()) {
//            user = new siswa();
//            user.setNama(reset.getString("nama"));

        
       conn.commit();
       return s;
    }

 public List <TU> getAllpresensi() throws SQLException {
        PreparedStatement psmt = null;
        ResultSet rset = null;
//        String tanggal = tgl;
        conn.setAutoCommit(false);
        String sql = "SELECT s.nama, p.nis, s.kelas, "
                + "sum(DECODE(p.kodekehadiran,2, kodekehadiran))/2, "
                + "sum(DECODE(p.kodekehadiran,3, kodekehadiran))/3, "
                + "sum(DECODE(p.kodekehadiran,4, kodekehadiran))/4 "
                + "FROM rpltpresensi p join rpltsiswa s on (p.nis = s.nis)"
//                + "where p.nis = '" +cari
//                + "' and  s.nama = '"+cari
                +" group by p.nis, s.nama, s.kelas ORDER BY p.nis";
        psmt = conn.prepareCall(sql);
        rset = psmt.executeQuery();

        List<TU> tu = new ArrayList<TU>();
        while (rset.next()) {
            siswa s = new siswa();
            TU t = new TU();
           
            s.setNama(rset.getString(1));
            t.setNama(s);
             s.setNIS(rset.getString(2));
            t.setNis(s);
            s.setKelas(rset.getString(3));
            t.setKelas(s);
            s.setIjin(rset.getString(4));
            t.setIjin(s);
            s.setSakit(rset.getString(5));
            t.setSakit(s);
            s.setTidakmasuk(rset.getString(6));
            t.setTidakmasuk(s);
            tu.add(t);
        }
        conn.commit();
        return tu;
    }


 public List <TU> getcari(String cari) throws SQLException {
        PreparedStatement psmt = null;
        ResultSet rset = null;
        conn.setAutoCommit(false);
        String sql = "SELECT s.nama, p.nis, s.kelas, "
                + "sum(DECODE(p.kodekehadiran,2, kodekehadiran))/2, "
                + "sum(DECODE(p.kodekehadiran,3, kodekehadiran))/3, "
                + "sum(DECODE(p.kodekehadiran,4, kodekehadiran))/4 "
                + "FROM rpltpresensi p join rpltsiswa s on (p.nis = s.nis)"
                + "where p.nis like '%"+ cari.toUpperCase()
                + "%' or s.nama like '%"+ cari.toUpperCase()
                + "%' group by p.nis, s.nama, s.kelas ORDER BY p.nis";
        psmt = conn.prepareCall(sql);
        rset = psmt.executeQuery();

        List<TU> tu = new ArrayList<TU>();
        while (rset.next()) {
            siswa s = new siswa();
            TU t = new TU();

            s.setNama(rset.getString(1));
            t.setNama(s);
             s.setNIS(rset.getString(2));
            t.setNis(s);
            s.setKelas(rset.getString(3));
            t.setKelas(s);
            s.setIjin(rset.getString(4));
            t.setIjin(s);
            s.setSakit(rset.getString(5));
            t.setSakit(s);
            s.setTidakmasuk(rset.getString(6));
            t.setTidakmasuk(s);
            tu.add(t);
        }
        conn.commit();
        return tu;
    }

    }



